'''
本节内容为两张表进行关联

'''
import pandas as pd
df1 = pd.DataFrame({'Name':['San Zhang','Si Li'],
                    'Age':[20,30]})
df2 = pd.DataFrame({'Name':['Si Li','Wu Wang'],
                    'Gender':['F','M']})

'''
两张表进行合并
'''
df1.merge(df2, on='Name', how='left')
df1 = pd.DataFrame({'df1_name':['San Zhang','Si Li'],
                    'Age':[20,30]})
df2 = pd.DataFrame({'df2_name':['Si Li','Wu Wang'],
                    'Gender':['F','M']})
df1.merge(df2, left_on='df1_name', right_on='df2_name', how='left')
'''
合并考试成绩的时候，第一个表记录了语文成绩，第二个是数学成绩
'''
df1 = pd.DataFrame({'Name':['San Zhang'],'Grade':[70]})

df2 = pd.DataFrame({'Name':['San Zhang'],'Grade':[80]})
df1.merge(df2, on='Name', how='left', suffixes=['_Chinese','_Math'])
'''
多个参数连接类似于left join on
'''
df1 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],
                    'Age':[20, 21],
                    'Class':['one', 'two']})
df2 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],
                    'Gender':['F', 'M'],
                    'Class':['two', 'one']})
df1.merge(df2, on=['Name', 'Class'], how='left') # 正确的结果
'''
索引连接
'''
df1 = pd.DataFrame({'Age':[20,30]},
                    index=pd.Series(
                    ['San Zhang','Si Li'],name='Name'))


df2 = pd.DataFrame({'Gender':['F','M']},
                    index=pd.Series(
                    ['Si Li','Wu Wang'],name='Name'))
df1.join(df2, how='left')
'''
索引连接并对重复的值进行处理
'''
df1 = pd.DataFrame({'Grade':[70]},
                    index=pd.Series(['San Zhang'],
                    name='Name'))


df2 = pd.DataFrame({'Grade':[80]},
                    index=pd.Series(['San Zhang'],
                    name='Name'))


df1.join(df2, how='left', lsuffix='_Chinese', rsuffix='_Math')


df1 = pd.DataFrame({'Age':[20,21]},
                    index=pd.MultiIndex.from_arrays(
                    [['San Zhang', 'San Zhang'],['one', 'two']],
                    names=('Name','Class')))
df2 = pd.DataFrame({'Gender':['F', 'M']},
                    index=pd.MultiIndex.from_arrays(
                    [['San Zhang', 'San Zhang'],['two', 'one']],
                    names=('Name','Class')))
df1.join(df2)
# 总结索引连接和值连接，索引连接为how 值连接为on
'''
方向连接 默认行合并
'''
df1 = pd.DataFrame({'Name':['San Zhang','Si Li'],
                    'Age':[20,30]})
df2 = pd.DataFrame({'Name':['Wu Wang'], 'Age':[40]})
pd.concat([df1, df2])
'''
列合并 参数传1
'''
df2 = pd.DataFrame({'Grade':[80, 90]})

df3 = pd.DataFrame({'Gender':['M', 'F']})

pd.concat([df1, df2, df3], 1)
df2 = pd.DataFrame({'Name':['Wu Wang'], 'Gender':['M']})
pd.concat([df1, df2])

df2 = pd.DataFrame({'Grade':[80, 90]}, index=[1, 2])

pd.concat([df1, df2], 1)

pd.concat([df1, df2], axis=1, join='inner')
'''
针对两个班级的同学进行合并
'''
df1 = pd.DataFrame({'Name':['San Zhang','Si Li'],
                    'Age':[20,21]})


df2 = pd.DataFrame({'Name':['Wu Wang'],'Age':[21]})

pd.concat([df1, df2], keys=['one', 'two'])

'''
序列与表的合并 
'''
s = pd.Series(['Wu Wang', 21], index = df1.columns)

df1.append(s, ignore_index=True)
'''
副本上进行合并
'''
s = pd.Series([80, 90])

df1.assign(Grade=s)
'''
比较两个表 返回两张表所对应的不同的地方
'''
df1 = pd.DataFrame({'Name':['San Zhang', 'Si Li', 'Wu Wang'],
                        'Age':[20, 21 ,21],
                        'Class':['one', 'two', 'three']})


df2 = pd.DataFrame({'Name':['San Zhang', 'Li Si', 'Wu Wang'],
                        'Age':[20, 21 ,21],
                        'Class':['one', 'two', 'Three']})
df1.compare(df2)
'''
保持完整
'''
df1.compare(df2, keep_shape=True)
'''
组合 s1 和s2比较如果s1小于s2取s1，反之取s2 若s1为nan 返回nan
'''
def choose_min(s1, s2):
    s2 = s2.reindex_like(s1)
    res = s1.where(s1<s2, s2)
    res = res.mask(s1.isna()) # isna表示是否为缺失值，返回布尔序列
    return res
df1 = pd.DataFrame({'A':[1,2], 'B':[3,4], 'C':[5,6]})
df2 = pd.DataFrame({'B':[5,6], 'C':[7,8], 'D':[9,10]}, index=[1,2])
# B、c 为相同的索引，但是index第一个为0,1第二个frame为1,2 所以取最小值的对应得矩阵，反之为nan的矩阵
'''
    A    B    C   D
0 NaN  NaN  NaN NaN
1 NaN  4.0  6.0 NaN
2 NaN  NaN  NaN NaN
'''
df1.combine(df2, choose_min)